package ar.com.android.alco.dbHelpers;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLALCOHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "alcoapp.db";
	private static final int DB_VERSION_NUMBER = 2;
	
	public static final String DB_ALIMENTOS_TABLE_NAME = "alimentos";
	public static final String DB_DATOS_USUARIO_TABLE_NAME = "datos_usuario";
	public static final String DB_CONTROL_PESO_TABLE_NAME = "control_peso";
	public static final String DB_REGISTRO_COMIDAS_TABLE_NAME = "registro_comidas";
	public static final String DB_CATEGORIA_ALIMENTOS_TABLE_NAME = "categoria_alimentos";
	public static final String DB_VERSION_TABLE_NAME = "version";
	
	private static final String DB_VERSION_CREATE_SCRIPT = "CREATE TABLE " + 
																		DB_VERSION_TABLE_NAME +                            
																		" (idVersion INTEGER PRIMARY KEY, " +
																		"  version INTEGER " +
																		")";
	
	private static final String DB_CATEGORIA_ALIMENTOS_CREATE_SCRIPT = "CREATE TABLE " + 
																DB_CATEGORIA_ALIMENTOS_TABLE_NAME +                            
																" (idCategoria INTEGER PRIMARY KEY, " +
																"  descripcion VARCHAR(60) " +
																")";
	
	private static final String DB_REGISTRO_COMIDAS_CREATE_SCRIPT = "CREATE TABLE " + 
																		DB_REGISTRO_COMIDAS_TABLE_NAME +                            
																		" (idRegistroComidas INTEGER PRIMARY KEY, " +
																		"  fechaHoraRegistro DATE, " +
																		"  idRegistro INTEGER, " +
																		"  descripcion TEXT, " +
																		"  creditos DOUBLE " + 
																		")";
	
	private static final String DB_CONTROL_PESO_CREATE_SCRIPT = "CREATE TABLE " + 
																	DB_CONTROL_PESO_TABLE_NAME +                            
																	" (idControlPeso INTEGER PRIMARY KEY, " +
																	"  fechaControl DATE, " +
																	"  peso DOUBLE, " +
																	"  creditosDisp DOUBLE " +
																	")";
	
	private static final String DB_ALIMENTOS_CREATE_SCRIPT = "CREATE TABLE " + 
																DB_ALIMENTOS_TABLE_NAME +                            
																" (idAlimento INTEGER PRIMARY KEY, " +
																"  idCategoriaAlimento INTEGER, " +
																"  descripcion VARCHAR(60), " + 
																"  calorias DOUBLE, " +
																"  equivalencia VARCHAR(40), " +
																"  creditos DOUBLE " +
																")";
	
	private static final String DB_DATOS_USUARIO_CREATE_SCRIPT = "CREATE TABLE " + 
																	DB_DATOS_USUARIO_TABLE_NAME +                            
																	" (idDatosUsuario INTEGER PRIMARY KEY, " +
																	"  nombreApellido VARCHAR(60), " +
																	"  email VARCHAR(100), " +
																	"  sexo VARCHAR(1), " +
																	"  fase VARCHAR(1), " +
																	"  pesoInicio DOUBLE, " +
																	"  pesoPosible DOUBLE, " +
																	"  altura DOUBLE, " +
																	"  IMC DOUBLE, " +
																	"  creditosInicio DOUBLE, " +
																	"  idUsuarioALCO INTEGER" +
																	")";
	
	private static SQLALCOHelper instance;
	
    public static synchronized SQLALCOHelper getHelper(Context context)
    {
        if (instance == null)
            instance = new SQLALCOHelper(context);

        return instance;
    }
	
	private SQLALCOHelper(Context context) {
		super(context, DATABASE_NAME, null, DB_VERSION_NUMBER);
	}
	
	public void dbClose() {
		this.close();
	}
	public void dbOpen() throws SQLException {
		this.getWritableDatabase();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			Log.i("onCreate", "Creating the database...");
			db.execSQL(DB_REGISTRO_COMIDAS_CREATE_SCRIPT);
			db.execSQL(DB_ALIMENTOS_CREATE_SCRIPT);
			db.execSQL(DB_DATOS_USUARIO_CREATE_SCRIPT);
			db.execSQL(DB_CONTROL_PESO_CREATE_SCRIPT);
			db.execSQL(DB_VERSION_CREATE_SCRIPT);
			db.execSQL(DB_CATEGORIA_ALIMENTOS_CREATE_SCRIPT);
		} catch (Exception e) {
			Log.e("onCreate", "Error with creating Database Tables: " + e);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("onUpgrade", "Update the database...");
		if(oldVersion == 1) {
			db.execSQL("ALTER TABLE " + DB_DATOS_USUARIO_TABLE_NAME + " ADD COLUMN email VARCHAR(100)");
			db.execSQL("ALTER TABLE " + DB_DATOS_USUARIO_TABLE_NAME + " ADD COLUMN idUsuarioALCO INT");
		}
//		db.execSQL("DROP TABLE " + DB_TYPEFOOD_TABLE_NAME);
//		db.execSQL("DROP TABLE " + DB_FOOD_TABLE_NAME);
//		db.execSQL("DROP TABLE " + DB_TABLES_TABLE_NAME);
//		db.execSQL("DROP TABLE " + DB_ORDER_TABLE_NAME);
//		db.execSQL("DROP TABLE " + DB_ORDERCHILD_TABLE_NAME);
	}
	
	public void beginTransaction()
	{
		try {
			this.getWritableDatabase().beginTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in Begintransaction Method: " + e);
		}		
	}
	
	public void commitTransaction()
	{
		try {
			this.getWritableDatabase().setTransactionSuccessful();
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in CommitTransaction Method: " + e);
		}		
	}
	
	public void rollbackTransaction()
	{
		try {
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in RollbackTransaction Method: " + e);
		}		
	}
}
